package com.data.migration.service;

import com.data.migration.entity.Operation;
import com.data.migration.util.DBUtil;
import com.data.migration.vo.DBMetadata;
import com.data.migration.vo.Table;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by Peter on 2018/12/8.
 */

/**
 * A版添加两个列，app_type和t_id(青少版数据的id)，为数据迁移做好准备
 */
@Service
public class Step_0 {
    public static final String step = "0";
    public static final String stepDesc = "A版添加两个列，app_type和t_id(青少版数据的id)，为数据迁移做好准备";
    private static final Logger logger = LoggerFactory.getLogger(Step_0.class);


    public static List<Operation> operations_Add = new ArrayList<>();
    public static List<Operation> operations_Delete = new ArrayList<>();

    public static void main(String[] args) throws SQLException {
        Step_0 step_1 = new Step_0();
//        step_1.start(); // 添加列
        step_1.clean(); // 删除列
    }

    public void start() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== Step 0: 数据库准备工作 start =========================" +
                "\n******************************************************************************");
        addColumnForA();
        logger.info("\n******************************************************************************" +
                "\n======================== Step 0: 数据库准备工作 end ==========================" +
                "\n******************************************************************************\n");
    }

    public void clean() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 0: 数据库准备工作 start =========================" +
                "\n******************************************************************************");
        deleteColumnForA();
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 0: 数据库准备工作 end =========================" +
                "\n******************************************************************************");
    }

    // A版添加列
    public void addColumnForA() throws SQLException {
        logger.info("\n     --------- 0.1 添加列sql start --------- ");
        prepareAddTIdSql();
        DBUtil.execSqlBatch(operations_Add);
    }

    // A版删除列
    public void deleteColumnForA() throws SQLException {
        logger.info("\n     ---------【clean】 0.1 删除列sql start --------- ");
        prepareDeleteTIdSql();
        DBUtil.execSqlBatch(operations_Delete);
    }

    public void prepareAddTIdSql() {
        Iterator<String> keys = DBMetadata.A.keySet().iterator();
        while (keys.hasNext()) {
            String key = keys.next();
            Table table = DBMetadata.A.get(key);
            String add_sql_1 = new String("alter table " + table.getName() + " add app_type char(1) DEFAULT 'A' after " + table.getColumns().get(0).getName() + ";");
            String add_sql_2 = new String("alter table " + table.getName() + " add t_id int after " + table.getColumns().get(0).getName() + ";");
            Operation opera1 = new Operation(Operation.app_type_A, Operation.operation_type_U, add_sql_1, step, stepDesc);
            Operation opera2 = new Operation(Operation.app_type_A, Operation.operation_type_U, add_sql_2, step, stepDesc);
            operations_Add.add(opera1);
            operations_Add.add(opera2);
        }

        // 特殊的添加列
        // 学生表单独添加列 t_memb_balance
        String add_t_memb_balance = new String("alter table td_nt_member add t_memb_balance decimal(16,2) NOT NULL DEFAULT 0 after MEMB_BALANCE;");
        Operation opera_t_memb_balance = new Operation(Operation.app_type_A, Operation.operation_type_U, add_t_memb_balance, step, stepDesc);
        operations_Add.add(opera_t_memb_balance);

        // 老师表单独添加列 t_tea_balance
        String add_t_tea_balance = new String("alter table td_nt_teacher add t_tea_balance decimal(16,2) NOT NULL DEFAULT 0 after TEA_BALANCE;");
        Operation opera_t_tea_balance = new Operation(Operation.app_type_A, Operation.operation_type_U, add_t_tea_balance, step, stepDesc);
        operations_Add.add(opera_t_tea_balance);
    }

    // 恢复初始化，删除添加的列
    public void prepareDeleteTIdSql() {
        Iterator<String> keys = DBMetadata.A.keySet().iterator();
        while (keys.hasNext()) {
            String key = keys.next();
            Table table = DBMetadata.A.get(key);
            String delete_sql_1 = new String("alter table " + table.getName() + " DROP app_type" + ";");
            String delete_sql_2 = new String("alter table " + table.getName() + " DROP t_id" + ";");

            Operation opera1 = new Operation(Operation.app_type_A, Operation.operation_type_U, delete_sql_1, step, stepDesc);
            Operation opera2 = new Operation(Operation.app_type_A, Operation.operation_type_U, delete_sql_2, step, stepDesc);
            operations_Delete.add(opera1);
            operations_Delete.add(opera2);
        }

        // 删除特殊的列
        // 学生 t_memb_balance
        String add_t_memb_balance = new String("alter table td_nt_member drop t_memb_balance;");
        Operation opera_t_memb_balance = new Operation(Operation.app_type_A, Operation.operation_type_U, add_t_memb_balance, step, stepDesc);
        operations_Delete.add(opera_t_memb_balance);

        // 老师 t_tea_balance
        String add_t_tea_balance = new String("alter table td_nt_teacher drop t_tea_balance;");
        Operation opera_t_tea_balance = new Operation(Operation.app_type_A, Operation.operation_type_U, add_t_tea_balance, step, stepDesc);
        operations_Delete.add(opera_t_tea_balance);

    }


}
